package apibasej.db.server.utilmetadata;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map.Entry;

import apibasej.basic.exception.APIBaseException;
import apibasej.db.metadata.Col;
import apibasej.db.metadata.Tab;


/**
 * http://code.google.com/p/apibasej/
 * 
 * @author Julio Azevedo Lessa - apibasej@gmail.com
 * 
 */
public class LoaderModelDB {

	private String catalog;
	private String schema;
	private boolean appendSchemaOnTabSql = false;
	//private String tableNamePattern;// irá usar listas de tabelas inclusivas ou exclusivas
	
//	private String url;
//	private String user;
//	private String pass;
	private Connection conn;
	
	//private ArrayList<String> tableNames = new ArrayList<String>();// CASE SENSITIVE ?!
	//private ArrayList<String> ignoredTables = new ArrayList<String>();// se não setar as tabelas lê as tabelas do banco e ignora estas
	
	private boolean ignorePksAndFks = false;// para drivers como access que não suporta leitura de PKS e FKS
	
	//key:nomeTable , val:idTable
	private LinkedHashMap<String,Integer> tables = new LinkedHashMap<>();
	
	// nome_tab.nome_col
	private ArrayList<String> indirectForeignCols = new ArrayList<>();
	
	private ArrayList<String> tabsToIgn = new ArrayList<>();//não tem muito sentido, pois basta não adicionar para gerar ...
	private ArrayList<String> colsToIgn = new ArrayList<>();//deve setar com o nome da tabela
	
	// ver !!! ver opção de adicionar imports para as classes !!!
	// aqui seta o className para uma coluna específica
	//public void setReplaceClassName(String nameTab, String nameCol, String newClassName){
		// ???
	//}
	
	public ArrayList<String> getIndirectForeignCols() {
		return indirectForeignCols;
	}
	public ArrayList<String> getColsToIgn() {
		return colsToIgn;
	}
	
	// aqui substitui todos os tipos
	private HashMap<Class<?>,Class<?>> replaceClass = new HashMap<Class<?>, Class<?>>();
	public void setReplaceClass(Class<?> oldClass, Class<?> newClass){
		replaceClass.put(oldClass, newClass);
	}
	
	//private ArrayList<Tab> tabs = new ArrayList<Tab>();
	
	//private TabGroup tabGroup = new TabGroup();
	
	private LinkedHashMap<String,Tab> tabs = new LinkedHashMap<String, Tab>();
	
	public LoaderModelDB(String url, String user, String pass) throws SQLException {
		conn = DriverManager.getConnection(url,user,pass);
	}
	public LoaderModelDB(Connection conn) throws SQLException {
		this.conn = conn;
	}


	@SuppressWarnings({"unchecked","rawtypes"})
	public void load() throws Exception{
		
		try{
			//DriverManager.registerDriver(new Driver());// chamar no main
			//conn = DriverManager.getConnection(getUrl(),getUser(),getPass());
			DatabaseMetaData dmd = conn.getMetaData();
			
			// ************* TABS E COLS ****************************************************
			if(getTables().size()==0){//getTableNames().size()==0) {
				//loadTableNamesFromMetadata(dmd);
				throw new APIBaseException("É preciso definir as tabelas e os IDs");
			}
			for(Entry<String,Integer> et : getTables().entrySet()){ //
				
				try{
					//tabGroup, 
					Tab tab = new Tab(getSchema(), et.getKey(), et.getValue(), isAppendSchemaOnTabSql());
					tabs.put(tab.getName(), tab);
					
					if(tabsToIgn!=null && tabsToIgn.contains(tab.getName())){
						System.out.println("TABELA IGNORADA: "+tab.getName());
					}

					//System.out.println("TAB>"+tab.getName()+" - "+tab.getSchema()+" - "+tab.getStrForSql());
					
					UtilDatabaseMetadata.createColsInEmptyTab(tab, conn, replaceClass, getIndirectForeignCols(), colsToIgn);
					/*
					ResultSet rsAux = conn.prepareStatement("SELECT * FROM "+(isAppendSchemaOnTabSql()?getSchema()+".":"")+tab.getName()+" WHERE 1=0 ").executeQuery();//dmd.getColumns(null, "public", tabName, null);
					ResultSetMetaData rsmd = rsAux.getMetaData();
					for(int i=1;i<=rsmd.getColumnCount();i++){
						String name = rsmd.getColumnName(i);
						String className = rsmd.getColumnClassName(i);
						Class<?> classCol = Class.forName(className);
						if(replaceClass.containsKey(classCol)) classCol = replaceClass.get(classCol);
		 				
						int displaySize = rsmd.getColumnDisplaySize(i);// qtd caracteres exibição
						int precision = rsmd.getPrecision(i);
						int scale = rsmd.getScale(i);
						int type = rsmd.getColumnType(i);
						String typeName = rsmd.getColumnTypeName(i);
						boolean notNull = rsmd.isNullable(i)==0;
						boolean signed = rsmd.isSigned(i);
						
						String nameColWithTab = tab.getName()+"."+name;
						boolean isIndirectForeignCol = getIndirectForeignCols().contains(nameColWithTab);
						
						//ao criar a col já adiciona na tab pai
						new Col(tab, name, classCol, displaySize, precision, scale, type, typeName, notNull, signed, null, null, isIndirectForeignCol);
					}*/
				}catch (Exception e) {
					System.out.println("ERRO AO LER TABELA: "+et.getKey()+", ERRO: "+e);
				}

			}
			
			
			if(!isIgnorePksAndFks()){
				for(Tab tab : tabs.values()){
					//System.out.println("TTTTTTTT>"+tab.getName());
					
					// ************* PKS ****************************************************
					ResultSet pks = dmd.getPrimaryKeys(getCatalog(), getSchema(), tab.getName());
					//AuxIO.printTab(pks,28);
					while(pks.next()){
						String colName = pks.getString("column_name");
						//System.out.println("PK>"+colName);
						if(colName==null || tab.getCol(colName)==null) continue;// tratar????
						tab.getCol(colName).setPk(true);
					}
					pks.close();
					
					// ************* FKS ****************************************************
					ResultSet fks = dmd.getExportedKeys(getCatalog(), getSchema(), tab.getName());
					while(fks.next()){
						//AuxIO.printTab(fks,28);
						String fktable = fks.getString("fktable_name");
						String fkcolumn = fks.getString("fkcolumn_name");
						String pktable = fks.getString("pktable_name");
						String pkcolumn = fks.getString("pkcolumn_name");
						Tab fTab = getTab(fktable);
						if(fTab==null){
							continue;//pode continuar! pois depois adicionará a fk no outro sentido
							//String info = "fktable="+fktable+", fkcolumn="+fkcolumn+", pktable="+pktable+", pkcolumn="+pkcolumn;
							//throw new NullPointerException(" ("+info+")");
						}
						Col c = fTab.getCol(fkcolumn); // Col<?> não deixa setar
						//if(c==null){
						//	continue; // tratar ?????
						//}
						//Tab tabExterna = getTab(pktable);
						
						Col<?> colExt = getTab(pktable).getCol(pkcolumn);
						c.setForeignCol( colExt );
						//c.setForeignTable(pktable);
						//c.setForeignTableCol(pkcolumn);
					}
					fks.close();
				}
			}
		}finally{
			if(conn!=null)conn.close();
		}

		
	}

	/*
	public void loadTableNamesFromMetadata(DatabaseMetaData dmd) throws SQLException{
		ResultSet ts = dmd.getTables(getCatalog(), getSchema(), null, isIgnoreViews()?new String[]{"TABLE"}:new String[]{"TABLE","VIEW"});
		while(ts.next()){
			//AuxIO.printTab(ts,28);
			String tabName = ts.getString("table_name");
			if( !getIgnoredTables().contains(tabName) ){
				tableNames.add(tabName);
			}
		}
		ts.close();
	}*/
	
	public LinkedHashMap<String, Integer> getTables() {
		return tables;
	}
	
	private boolean ignoreViews = false;
	public boolean isIgnoreViews() {
		return ignoreViews;
	}
	public void setIgnoreViews(boolean ignoreViews) {
		this.ignoreViews = ignoreViews;
	}
	

	public LinkedHashMap<String, Tab> getTabs() {
		return tabs;
	}
	public String getCatalog() {
		return catalog;
	}
	public void setCatalog(String catalog) {
		this.catalog = catalog;
	}
	public String getSchema() {
		return schema;
	}
	public void setSchema(String schema) {
		this.schema = schema;
	}
	public Tab getTab(String tabName){
		return tabs.get(tabName);
	}
	public boolean isAppendSchemaOnTabSql() {
		return appendSchemaOnTabSql;
	}
	public void setAppendSchemaOnTabSql(boolean appendSchemaOnTabSql) {
		this.appendSchemaOnTabSql = appendSchemaOnTabSql;
	}
	public boolean isIgnorePksAndFks() {
		return ignorePksAndFks;
	}
	public void setIgnorePksAndFks(boolean ignorePksAndFks) {
		this.ignorePksAndFks = ignorePksAndFks;
	}
	
	@Override
	protected void finalize() throws Throwable {
		super.finalize();
		if(conn!=null) conn.close();
	}
	
}
